package db;


public class OracleNativeStatements extends AbstractDbNativeStatements {
	
	
	
	public String getAllColumnsSql(String schema, String table) {
		return " SELECT col.column_name AS COLUMN_NAME, col.data_type AS DATA_TYPE, col.data_length AS DATA_LENGTH, col.nullable AS NULLABLE, com.comments AS COLUMN_COMMENT" +
						" FROM  all_tab_columns col, all_col_comments com " +
						" WHERE col.owner = '" + schema.toUpperCase() + "'" +
						"    AND col.table_name = '" + table + "'" +
						"    AND col.owner = com.owner " +
						"    AND col.table_name = com.table_name " +
						"    AND col.column_name = com.column_name " +
						" ORDER BY col.column_id ";
	}


	public String getAllTablesSql(String schema) {
		return " SELECT table_name AS TABLE_NAME " +
		  " FROM   all_tables " +
		  " WHERE owner = '" + schema.toUpperCase() + "'";
	}
	
	public String getPrimaryKeyColumns(String schema, String table) {
		return "select i.column_name AS COLUMN_NAME " +
				" from all_constraints c, all_ind_columns i " +
				" where c.owner = '" + schema.toUpperCase() + "' " +
				"        AND c.table_name = '"  + table + "' " +
				"        AND c.constraint_type = 'P' " +
				"        AND c.owner = i.index_owner " +
				"        AND c.constraint_name = i.index_name ";
	}
	
	public String getReferencesSql(String schema, String table) {
		return "SELECT col1.owner AS TABLE_SCHEMA, col1.table_name AS TABLE_NAME, col1.column_name AS COLUMN_NAME, " +
				" col2.owner AS R_TABLE_SCHEMA, col2.table_name AS R_TABLE_NAME, " +
				" (SELECT data_type FROM  all_tab_columns col WHERE col.owner = col1.owner AND col.table_name = col1.table_name AND col.column_name = col1.column_name) AS DATA_TYPE, " +
				" col2.column_name AS R_COLUMN_NAME, " +
				" (SELECT data_type FROM  all_tab_columns col WHERE col.owner = col2.owner AND col.table_name = col2.table_name AND col.column_name = col2.column_name) AS R_DATA_TYPE " +
				" FROM all_cons_columns col1, all_constraints cons1, all_cons_columns col2, all_constraints cons2" +
				" WHERE col1.owner = '" + schema.toUpperCase() + "'" +
				"        AND col1.constraint_name = cons1.constraint_name" +
				"        AND cons1.constraint_type = 'R'" +
				"        AND col2.constraint_name = cons1.r_constraint_name" +
				"        AND col2.constraint_name = cons2.constraint_name" +
				"        AND (col1.table_name = '"  + table + "' OR col2.table_name = '"  + table + "')";
	}
	
	public String getIndexesSql(String schema, String table) {
		return " SELECT ind.index_name AS INDEX_NAME, ind.column_name AS COLUMN_NAME, " +
				"     (SELECT data_type FROM all_tab_columns col WHERE col.owner = ind.table_owner AND col.table_name = ind.table_name AND col.column_name = ind.column_name) AS DATA_TYPE, " +
				"     des.uniqueness AS UNIQUENESS " +
				" FROM all_ind_columns ind, all_indexes des " +
				" WHERE ind.table_owner = '" + schema.toUpperCase() + "'" +
				" AND ind.table_name = '"  + table + "' " +
				" AND ind.table_owner = des.table_owner " +
				" AND ind.index_name = des.index_name" +
				" ORDER BY ind.index_name ";
	}
}
